The present invention relates generally to information processing environments and, more particularly, to information access in a data processing system, such as a Database Management System (DBMS).
Computers are very powerful tools for storing and providing access to vast amounts of information. Computer databases are a common mechanism for storing information on computer systems while providing easy access to users. A typical database is an organized collection of related information stored as "records" having "fields" of information. As an example, a database of employees may have a record for each employee where each record contains fields designating specifics about the employee like name, home address, salary, and the like.
Between the actual physical database itself (i.e., the data actually stored on a storage device) and the users of the system, a database management system or DBMS is provided as a software cushion or layer. In essence, the DBMS shields the database user from knowing or even caring about underlying hardware-level details. Typically, all requests from users for access to the data are processed by the DBMS. For example, information may be added or removed from data files, information retrieved from or updated in such files, and so forth, all without knowledge of underlying system implementation. In this manner, the DBMS provides users with a conceptual view of the database that is removed from the hardware level. The general construction and operation of a database management system is known in the art. See e.g., Date, C., An Introduction to Database Systems, Volume I and II, Addison Wesley, 1990; the disclosure of which is hereby incorporated by reference.
DBMS systems have long since moved from a centralized mainframe environment to a de-centralized or distributed desktop environment--appearing as Personal Computer Database Management Systems (PC DBMS) on the desktops of most (PC) users. Common examples of PC DBMSs include Borland's DBASE.RTM. and Paradox.RTM.. More recently, PC DBMS systems have been connected via a network to a server-based database system (SQL database server) to form a client/server database system, such as connecting DBASE.RTM. clients to an Interbase.RTM. database server. As the migration to client/server continues, more and more PC end users are connected to served-based SQL database systems each day.
This has created a problem, however. Users of PC DBMS have come to expect certain navigational functionality from a database system. Specifically, users expect bidirectional scrolling, such as is common during a "browse" mode of operation. Additionally, users expect to be able to jump to a particular location in the database file, the location being specified either as an offset or as a particular record number. Examples of the latter include the dBASE commands of GOTO TOP, GOTO BOTTOM, and GOTO RECNO(). All told, there are certain lower-level "verbs" which users expect when using a database system on the desktop.
Although the task of providing such functionality for a record-based or navigational database system is fairly straightforward, the task becomes problematic when attempted in an SQL-based database server environment. In particular, SQL databases are set-oriented; they have no notion of physical record locations, such as record numbers and record offsets. When the client attaches to a database via a SQL driver, the server has no context information such as the current index or the current position within the index. Thus, the client must maintain all this information, generating requests to change the current record.
How SQL-oriented systems try to address the problem is to emulate the desired action on the client side using clever caching mechanisms and issuing one or more SQL queries. Consider, for instance, a user request to scroll backwards through a group of records. This request would be satisfied by a system issuing a SQL query specifying a sort, in descending order, on a particular field. The database server, in response to such SQL commands, would provide the client with an answer set (i.e., copy of records satisfying the SQL query). The client then scrolls forward through the answer set, but, since it is sorted in descending order, it appears to the client user that he or she is scrolling backwards through the records. There are inherent drawbacks to this strategy, though. The strategy fails when the user traverses off the end of the cache. Also, the cached records will not be up-to-date with the current value on the server. A similar disadvantage occurs when a PC client requests a particular record order (i.e., set order to a particular index). Since SQL servers are set-oriented, the operation of setting to an index requires throwing away all current requests, looking up the new index, and emulating traversal on that index by sorting on the columns of the index.
Although an emulation approach is workable, it occurs a substantial performance penalty for the database system. For instance, the client must generate an appropriate SQL query which the database server, in turn, must parse and process. Further, as part of the approach, the server must perform a sort--a task which consumes substantial resources for the large database tables which are typically employed in corporate settings. Moreover, as a PC database user typically employs one or more indexes for specifying a sort order for the database, the client must take into account these indexes when specifying the descending sort.
The SQL set-oriented model is inadequate for handling the navigational model of database access common to PC databases. What is needed are systems and methods which provide the convenience and familiarity users associate with navigational databases, yet provide that in the context of an SQL database environment. The present invention fulfills this and other needs.